﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace MMK.PaketTracker.Server.Database
{
		/// <summary>
		/// BaseSqlDataLayer
		/// </summary>
	public class BaseSqlDataLayer : IDisposable
	{
		private SqlConnection transactionConnection;
		private SqlTransaction transaction;
		private SqlCommand transactionCommand;

		/// <summary>
		/// Initializes a new instance of the <see cref="T:BaseSqlDataLayer"/> class.
		/// </summary>
		public BaseSqlDataLayer()
		{
		}

		/// <summary>
		/// Initializes the specified connection string.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		public void Initialize( string connectionString )
		{
			this.connectionString = connectionString;
		}

		private string connectionString;

		/// <summary>
		/// Gets the connection string.
		/// </summary>
		/// <value>The connection string.</value>
		protected string ConnectionString
		{
			get
			{
				return this.connectionString;
			}
		}

		/// <summary>
		/// Opens the connection.
		/// </summary>
		/// <returns></returns>
		protected SqlConnection OpenConnection()
		{
			SqlConnection conn = new SqlConnection( this.ConnectionString );
			conn.Open();
			return conn;
		}

		/// <summary>
		/// Closes the connection.
		/// </summary>
		/// <param name="conn">The conn.</param>
		protected void CloseConnection( SqlConnection conn )
		{
			try
			{
				conn.Close();
			}
			catch
			{
			}
		}

		/// <summary>
		/// Gets the data reader.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		protected virtual SqlDataReader GetDataReader( string sql )
		{
			SqlCommand cmd = new SqlCommand( sql, this.OpenConnection() );
			return cmd.ExecuteReader( CommandBehavior.CloseConnection );
		}

		/// <summary>
		/// Gets the data reader.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual SqlDataReader GetDataReader( string sql, params SqlParameter[] parameters )
		{
			SqlCommand cmd = new SqlCommand( sql, this.OpenConnection() );
			if( parameters != null )
			{
				foreach( SqlParameter parameter in parameters )
				{
					if( parameter != null )
					{
						cmd.Parameters.Add( parameter );
					}
				}
			}
			return cmd.ExecuteReader( CommandBehavior.CloseConnection );
		}

		/// <summary>
		/// Gets the data reader from stored procedure.
		/// </summary>
		/// <param name="name">The name.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual SqlDataReader GetDataReaderFromStoredProcedure( string name, params SqlParameter[] parameters )
		{
			SqlCommand cmd = new SqlCommand( name, this.OpenConnection() );
			cmd.CommandType = CommandType.StoredProcedure;
			if( parameters != null )
			{
				foreach( SqlParameter parameter in parameters )
				{
					if( parameter != null )
					{
						cmd.Parameters.Add( parameter );
					}
				}
			}
			return cmd.ExecuteReader( CommandBehavior.CloseConnection );
		}

		/// <summary>
		/// Executes the non query.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		protected virtual int ExecuteNonQuery( string sql )
		{
			return ExecuteNonQuery( sql, null );
		}

		/// <summary>
		/// Executes the non query.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual int ExecuteNonQuery( string sql, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( sql, connection );
				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				return cmd.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Executes the non query.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="identity">The identity.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual int ExecuteNonQuery( string sql, out int identity, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( sql, connection );
				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				int result = cmd.ExecuteNonQuery();
				identity = GetIdentity( connection );
				return result;
			}
		}

		/// <summary>
		/// Executes the stored procedure.
		/// </summary>
		/// <param name="name">The name.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual int ExecuteStoredProcedure( string name, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( name, connection );
				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				cmd.CommandType = CommandType.StoredProcedure;
				return cmd.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Executes the stored procedure.
		/// </summary>
		/// <param name="connection">The connection.</param>
		/// <param name="name">The name.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual int ExecuteStoredProcedure( SqlConnection connection, string name, params SqlParameter[] parameters )
		{
			SqlCommand cmd = new SqlCommand( name, connection );
			if( parameters != null )
			{
				foreach( SqlParameter parameter in parameters )
				{
					if( parameter != null )
					{
						cmd.Parameters.Add( parameter );
					}
				}
			}
			cmd.CommandType = CommandType.StoredProcedure;
			return cmd.ExecuteNonQuery();
		}

		/// <summary>
		/// Executes the stored procedure.
		/// </summary>
		/// <param name="name">The name.</param>
		/// <param name="identity">The identity.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual int ExecuteStoredProcedure( string name, out int identity, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( name, connection );
				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				cmd.CommandType = CommandType.StoredProcedure;
				int result = cmd.ExecuteNonQuery();
				identity = this.GetIdentity( connection );
				return result;
			}
		}

		/// <summary>
		/// Gets the scalar.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		protected virtual object GetScalar( string sql )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( sql, connection );
				return cmd.ExecuteScalar();
			}
		}

		/// <summary>
		/// Gets the scalar.
		/// </summary>
		/// <param name="connection">The connection.</param>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		protected virtual object GetScalar( SqlConnection connection, string sql )
		{
			SqlCommand cmd = new SqlCommand( sql, connection );
			return cmd.ExecuteScalar();
		}

		/// <summary>
		/// Gets the scalar.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual object GetScalar( string sql, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( sql, connection );
				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				return cmd.ExecuteScalar();
			}
		}

		/// <summary>
		/// Gets the scalar.
		/// </summary>
		/// <param name="connection">The connection.</param>
		/// <param name="sql">The SQL.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual object GetScalar( SqlConnection connection, string sql, params SqlParameter[] parameters )
		{
			SqlCommand cmd = new SqlCommand( sql, connection );
			if( parameters != null )
			{
				foreach( SqlParameter parameter in parameters )
				{
					if( parameter != null )
					{
						cmd.Parameters.Add( parameter );
					}
				}
			}
			return cmd.ExecuteScalar();
		}

		/// <summary>
		/// Gets the scalar from stored procedure.
		/// </summary>
		/// <param name="name">The name.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual object GetScalarFromStoredProcedure( string name, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( name, connection );
				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				cmd.CommandType = CommandType.StoredProcedure;
				return cmd.ExecuteScalar();
			}
		}

		/// <summary>
		/// Gets the data table.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		protected virtual DataTable GetDataTable( string sql )
		{
			return GetDataTable( sql, null, new SqlParameter[] { } );
		}

		/// <summary>
		/// Gets the data table.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual DataTable GetDataTable( string sql, params SqlParameter[] parameters )
		{
			return GetDataTable( sql, null, parameters );
		}

		/// <summary>
		/// Gets the data table.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="table">The table.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual DataTable GetDataTable( string sql, DataTable table, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( sql, connection );
				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				SqlDataAdapter adapter = new SqlDataAdapter( cmd );
				if( table == null )
				{
					table = new DataTable();
				}
				adapter.Fill( table );
				return table;
			}
		}

		/// <summary>
		/// Gets the data table from stored procedure.
		/// </summary>
		/// <param name="name">The name.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		protected virtual DataTable GetDataTableFromStoredProcedure( string name, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( name, connection );
				cmd.CommandType = CommandType.StoredProcedure;

				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				SqlDataAdapter adapter = new SqlDataAdapter( cmd );
				DataTable table = new DataTable();
				adapter.Fill( table );
				return ( table );
			}
		}

		/// <summary>
		/// Gets the identity.
		/// </summary>
		/// <param name="connection">The connection.</param>
		/// <returns></returns>
		protected virtual int GetIdentity( SqlConnection connection )
		{
			SqlCommand cmd = new SqlCommand( "SELECT @@Identity;", connection );
			return Convert.ToInt32( cmd.ExecuteScalar() );
		}

		/// <summary>
		/// Checks the null.
		/// </summary>
		/// <param name="value">The value.</param>
		/// <returns></returns>
		protected object CheckNull( object value )
		{
			if( value == null )
			{
				return DBNull.Value;
			}
			return value;
		}

		/// <summary>
		/// Checks the null.
		/// </summary>
		/// <param name="dt">The dt.</param>
		/// <returns></returns>
		protected object CheckNull( DateTime dt )
		{
			if( ( dt == DateTime.MinValue ) || ( dt == DateTime.MaxValue ) )
				return DBNull.Value;
			return dt;
		}

		/// <summary>
		/// Checks the null.
		/// </summary>
		/// <param name="guid">The GUID.</param>
		/// <returns></returns>
		protected object CheckNull( Guid guid )
		{
			if( guid == Guid.Empty )
				return DBNull.Value;
			return guid;
		}

		/// <summary>
		/// Gets the string.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <returns></returns>
		protected string GetString( IDataReader reader, string field )
		{
			return ( reader[ field ] as string );
		}

		/// <summary>
		/// Gets the GUID.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <returns></returns>
		protected Guid GetGuid( IDataReader reader, string field )
		{
			if( reader[ field ] != DBNull.Value )
				return ( Guid ) reader[ field ];
			return Guid.Empty;
		}

		/// <summary>
		/// Gets the date time.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <returns></returns>
		protected DateTime GetDateTime( IDataReader reader, string field )
		{
			return this.GetDateTime( reader, field, DateTime.MinValue );
		}

		/// <summary>
		/// Gets the date time.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <param name="defaultValue">The default value.</param>
		/// <returns></returns>
		protected DateTime GetDateTime( IDataReader reader, string field, DateTime defaultValue )
		{
			if( reader[ field ] != DBNull.Value )
				return ( DateTime ) reader[ field ];
			return defaultValue;
		}

		/// <summary>
		/// Gets the int32.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <returns></returns>
		protected int GetInt32( IDataReader reader, string field )
		{
			return this.GetInt32( reader, field, 0 );
		}

		/// <summary>
		/// Gets the int32.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <param name="defaultValue">The default value.</param>
		/// <returns></returns>
		protected int GetInt32( IDataReader reader, string field, int defaultValue )
		{
			if( reader[ field ] != DBNull.Value )
				return ( int ) reader[ field ];
			return defaultValue;
		}

		/// <summary>
		/// Gets the int64.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <returns></returns>
		protected long GetInt64( IDataReader reader, string field )
		{
			return this.GetInt64( reader, field, 0 );
		}

		/// <summary>
		/// Gets the int64.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <param name="defaultValue">The default value.</param>
		/// <returns></returns>
		protected long GetInt64( IDataReader reader, string field, int defaultValue )
		{
			if( reader[ field ] != DBNull.Value )
				return ( long ) reader[ field ];
			return defaultValue;
		}

		/// <summary>
		/// Gets the decimal.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <returns></returns>
		protected decimal GetDecimal( IDataReader reader, string field )
		{
			return this.GetDecimal( reader, field, 0 );
		}

		/// <summary>
		/// Gets the decimal.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <param name="defaultValue">The default value.</param>
		/// <returns></returns>
		protected decimal GetDecimal( IDataReader reader, string field, long defaultValue )
		{
			if( reader[ field ] != DBNull.Value )
				return ( decimal ) reader[ field ];
			return defaultValue;
		}


		/// <summary>
		/// Gets the double.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <param name="defaultValue">The default value.</param>
		/// <returns></returns>
		protected double GetDouble( IDataReader reader, string field, double defaultValue )
		{
			if( reader[ field ] != DBNull.Value )
				return ( double ) reader[ field ];
			return defaultValue;
		}

		/// <summary>
		/// Gets the double.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <returns></returns>
		protected double GetDouble( IDataReader reader, string field )
		{
			return GetDouble( reader, field, 0 );
		}

		/// <summary>
		/// Gets the boolean.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <returns></returns>
		protected bool GetBoolean( IDataReader reader, string field )
		{
			return this.GetBoolean( reader, field, false );
		}

		/// <summary>
		/// Gets the boolean.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="field">The field.</param>
		/// <param name="defaultValue">if set to <c>true</c> [default value].</param>
		/// <returns></returns>
		protected bool GetBoolean( IDataReader reader, string field, bool defaultValue )
		{
			if( reader[ field ] != DBNull.Value )
				return ( bool ) reader[ field ];
			return defaultValue;
		}

		/// <summary>
		/// Creates the data relation.
		/// </summary>
		/// <param name="relationName">Name of the relation.</param>
		/// <param name="ds">The ds.</param>
		/// <param name="parentColumn">The parent column.</param>
		/// <param name="childColumn">The child column.</param>
		/// <returns></returns>
		protected DataRelation CreateDataRelation( string relationName, DataSet ds, string parentColumn, string childColumn )
		{
			string[] parent = parentColumn.Split( '.' );
			if( parent.Length != 2 )
			{
				throw new ArgumentException( "parentColumn must contain valid column name: \"table.colum\"", "parentColumn" );
			}

			string[] child = childColumn.Split( '.' );
			if( child.Length != 2 )
			{
				throw new ArgumentException( "childColumn must contain valid column name: \"table.colum\"", "childColumn" );
			}

			return new DataRelation( relationName, ds.Tables[ parent[ 0 ] ].Columns[ parent[ 1 ] ], ds.Tables[ child[ 0 ] ].Columns[ child[ 1 ] ] );
		}

		/// <summary>
		/// Saves the stream.
		/// </summary>
		/// <param name="connection">The connection.</param>
		/// <param name="column">The column.</param>
		/// <param name="pointer">The pointer.</param>
		/// <param name="stream">The stream.</param>
		/// <returns></returns>
		protected int SaveStream( SqlConnection connection, string column, byte[] pointer, Stream stream )
		{
			int bufferSize = 1024;
			SqlCommand cmd = new SqlCommand( string.Format( "UPDATETEXT {0} @Pointer @Offset 0 @Bytes", column ), connection );

			SqlParameter paramPointer = cmd.Parameters.Add( "@Pointer", SqlDbType.Binary, 16 );
			paramPointer.Value = pointer;

			SqlParameter paramData = cmd.Parameters.Add( "@Bytes", SqlDbType.Image, bufferSize );

			SqlParameter paramOffset = cmd.Parameters.Add( "@Offset", SqlDbType.Int );
			paramOffset.Value = 0;

			BinaryReader br = new BinaryReader( stream );
			byte[] buffer = br.ReadBytes( bufferSize );
			int offset = 0;

			while( buffer.Length > 0 )
			{
				paramData.Value = buffer;
				cmd.ExecuteNonQuery();
				offset += buffer.Length;
				paramOffset.Value = offset;
				buffer = br.ReadBytes( bufferSize );
			}
			return offset;
		}

		/// <summary>
		/// Merges the and update.
		/// </summary>
		/// <param name="newData">The new data.</param>
		/// <param name="sql">The SQL.</param>
		/// <param name="parameters">The parameters.</param>
		protected void MergeAndUpdate( DataTable newData, string sql, params SqlParameter[] parameters )
		{
			using( SqlConnection connection = this.OpenConnection() )
			{
				SqlCommand cmd = new SqlCommand( sql, connection );
				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							cmd.Parameters.Add( parameter );
						}
					}
				}
				SqlDataAdapter adapter = new SqlDataAdapter( cmd );
				DataSet ds = new DataSet();
				adapter.Fill( ds, newData.TableName );

				SqlCommandBuilder cb = new SqlCommandBuilder( adapter );
				ds.Merge( newData, false, MissingSchemaAction.Ignore );
				adapter.Update( ds.Tables[ 0 ] );
			}
		}

		protected virtual void BeginTransaction()
		{
			this.transactionConnection = OpenConnection();
			this.transaction = this.transactionConnection.BeginTransaction( "Transaction" );

			this.transactionCommand = this.transactionConnection.CreateCommand();
			this.transactionCommand.Connection = this.transactionConnection;
			this.transactionCommand.Transaction = this.transaction;
			//this.transactionCommand.Connection = this.transactionConnection;
		}

		protected virtual int ExecuteNonQueryTransaction( string sql, params SqlParameter[] parameters )
		{
			try
			{
				//using (SqlConnection connection = this.transactionConnection)
				//{
				this.transactionCommand.Parameters.Clear();
				this.transactionCommand.CommandText = sql;

				//SqlCommand cmd = new SqlCommand(sql, connection);
				//cmd.Transaction = this.transaction;

				if( parameters != null )
				{
					foreach( SqlParameter parameter in parameters )
					{
						if( parameter != null )
						{
							this.transactionCommand.Parameters.Add( parameter );
						}
					}
				}
				//cmd.CommandType = CommandType.Text;
				return this.transactionCommand.ExecuteNonQuery();
				//}
			}
			catch( Exception ex )
			{
				this.transaction.Rollback();
				throw new Exception( ex.ToString() );
			}
		}

		protected virtual int ExecuteNonQueryTransaction( string sql )
		{
			return ExecuteNonQueryTransaction( sql, null );
		}

		protected virtual void EndAndCommitTransaction()
		{
			try
			{
				this.transaction.Commit();
			}
			catch( Exception ex )
			{
				this.transaction.Rollback();
				throw new Exception( ex.ToString() );
			}
			finally
			{
				this.transactionConnection.Close();
				this.transactionConnection = null;
				this.transaction = null;
			}
		}





		/// <summary>
		/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
		/// </summary>
		void IDisposable.Dispose()
		{
			this.Dispose( true );
			GC.SuppressFinalize( this );
		}

		/// <summary>
		/// Disposes the specified dispose.
		/// </summary>
		/// <param name="dispose">if set to <c>true</c> [dispose].</param>
		protected virtual void Dispose( bool dispose )
		{
			if( dispose )
			{
				// ...
			}
		}
	}
}
